Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# WIDTH_BUCKET

Syntax

Description of the illustration width_bucket.gif

Purpose

`WIDTH_BUCKET` lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. (Compare this function with `NTILE`, which creates equiheight histograms.) Ideally each bucket is a closed-open interval of the real number line. For example, a bucket can be assigned to scores between 10.00 and 19.999... to indicate that 10 is included in the interval and 20 is excluded. This is sometimes denoted [10, 20).

For a given expression, `WIDTH_BUCKET` returns the bucket number into which the value of this expression would fall after being evaluated.

- `expr` is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If `expr` evaluates to null, then the expression returns null.

- `min_value` and `max_value` are expressions that resolve to the end points of the acceptable range for `expr`. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null.

- `num_buckets` is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.

  See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

When needed, Oracle Database creates an underflow bucket numbered 0 and an overflow bucket numbered `num_buckets`+1. These buckets handle values less than `min_value` and more than `max_value` and are helpful in checking the reasonableness of endpoints.

Examples

The following example creates a ten-bucket histogram on the `credit_limit` column for customers in Switzerland in the sample table `oe.customers` and returns the bucket number ("Credit Group") for each customer. Customers with credit limits greater than the maximum value are assigned to the overflow bucket, 11:

    SELECT customer_id, cust_last_name, credit_limit,
       WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit Group"
       FROM customers WHERE nls_territory = 'SWITZERLAND'
       ORDER BY "Credit Group";

    CUSTOMER_ID CUST_LAST_NAME       CREDIT_LIMIT Credit Group
    ----------- -------------------- ------------ ------------
            825 Dreyfuss                      500            1
            826 Barkin                        500            1
            853 Palin                         400            1
            827 Siegel                        500            1
            843 Oates                         700            2
            844 Julius                        700            2
            835 Eastwood                     1200            3
            840 Elliott                      1400            3
            842 Stern                        1400            3
            841 Boyer                        1400            3
            837 Stanton                      1200            3
            836 Berenger                     1200            3
            848 Olmos                        1800            4
            849 Kaurusmdki                   1800            4
            828 Minnelli                     2300            5
            829 Hunter                       2300            5
            852 Tanner                       2300            5
            851 Brown                        2300            5
            850 Finney                       2300            5
            830 Dutt                         3500            7
            831 Bel Geddes                   3500            7
            832 Spacek                       3500            7
            838 Nicholson                    3500            7
            839 Johnson                      3500            7
            833 Moranis                      3500            7
            834 Idle                         3500            7
            845 Fawcett                      5000           11
            846 Brando                       5000           11
            847 Streep                       5000           11